package com.ww.exercise.coroutine.mid.q17.opt

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import kotlinx.coroutines.*
import kotlinx.coroutines.sync.Semaphore
import kotlinx.coroutines.sync.withPermit
import java.sql.Connection
import java.sql.PreparedStatement
import java.util.concurrent.atomic.AtomicInteger
import kotlin.random.Random
import kotlin.system.measureTimeMillis

// 确保添加HikariCP依赖
// Maven:
// <dependency>
//     <groupId>com.zaxxer</groupId>
//     <artifactId>HikariCP</artifactId>
//     <version>5.0.1</version>
// </dependency>
// <dependency>
//     <groupId>mysql</groupId>
//     <artifactId>mysql-connector-java</artifactId>
//     <version>8.0.33</version>
// </dependency>

// 用户数据类
data class User(
    val id: Int,
    val username: String,
    val email: String,
    val age: Int,
    val registrationDate: Long
)

// 进度记录器（断点续插）
class ProgressRecorder(private val progressFile: String) {
    private val progress = AtomicInteger(0)

    init {
        try {
            java.io.File(progressFile).readText().toIntOrNull()?.let {
                progress.set(it)
            }
        } catch (e: Exception) {
            println("未找到进度记录，从0开始插入")
        }
    }

    fun getCurrentProgress() = progress.get()
    fun updateProgress(newProgress: Int) {
        progress.set(newProgress)
        GlobalScope.launch(Dispatchers.IO) {
            try {
                java.io.File(progressFile).writeText(newProgress.toString())
            } catch (e: Exception) {
                println("进度记录写入失败: ${e.message}")
            }
        }
    }

    fun reset() {
        progress.set(0)
        try {
            java.io.File(progressFile).delete()
        } catch (e: Exception) {
        }
    }
}

// 数据生成器
object UserDataGenerator {
    private val random = Random
    fun generateUsers(startId: Int, count: Int): List<User> {
        return (startId until startId + count).map { id ->
            val username = "user_${id}_${randomString(8)}"
            User(
                id = id,
                username = username,
                email = "${username}@example.com",
                age = random.nextInt(18, 65),
                registrationDate = System.currentTimeMillis() - random.nextLong(365L * 24 * 60 * 60 * 1000)
            )
        }
    }

    private fun randomString(length: Int): String {
        val chars = "abcdefghijklmnopqrstuvwxyz0123456789"
        return (1..length).map { chars[random.nextInt(chars.length)] }.joinToString("")
    }
}

// 数据库操作工具类（基于Hikari）
class UserDatabaseHelper(private val dataSource: HikariDataSource) {
    // 获取连接（自动管理连接释放）
    private suspend fun <T> withConnection(block: (Connection) -> T): T {
        return withContext(Dispatchers.IO) {
            dataSource.connection.use { connection -> // use自动关闭连接
                connection.autoCommit = false
                try {
                    val result = block(connection)
                    connection.commit()
                    result
                } catch (e: Exception) {
                    connection.rollback()
                    throw e
                }
            }
        }
    }

    suspend fun createTableIfNotExists() {
        withConnection { connection ->
            val sql = """
                CREATE TABLE IF NOT EXISTS users (
                    id INT PRIMARY KEY,
                    username VARCHAR(50) NOT NULL,
                    email VARCHAR(100) NOT NULL,
                    age INT NOT NULL,
                    registration_date BIGINT NOT NULL
                )
            """.trimIndent()
            connection.createStatement().execute(sql)
        }
    }

    suspend fun truncateTable() {
        withConnection { connection ->
            connection.createStatement().execute("TRUNCATE TABLE users")
        }
    }

    // 单条插入
    suspend fun insertUser(user: User) {
        withConnection { connection ->
            val sql = """
                INSERT INTO users (id, username, email, age, registration_date)
                VALUES (?, ?, ?, ?, ?)
            """.trimIndent()
            connection.prepareStatement(sql).use { stmt ->
                setUserParameters(stmt, user)
                stmt.executeUpdate()
            }
        }
    }

    // 批量插入
    suspend fun batchInsertUsers(users: List<User>): Int {
        if (users.isEmpty()) return 0

        return withConnection { connection ->
            val sql = """
                INSERT INTO users (id, username, email, age, registration_date)
                VALUES (?, ?, ?, ?, ?)
            """.trimIndent()
            connection.prepareStatement(sql).use { stmt ->
                users.forEach { user ->
                    setUserParameters(stmt, user)
                    stmt.addBatch()
                }
                stmt.executeBatch().sum()
            }
        }
    }

    private fun setUserParameters(stmt: PreparedStatement, user: User) {
        stmt.setInt(1, user.id)
        stmt.setString(2, user.username)
        stmt.setString(3, user.email)
        stmt.setInt(4, user.age)
        stmt.setLong(5, user.registrationDate)
    }
}

// 插入测试管理器
class InsertTestManager(
    private val dbHelper: UserDatabaseHelper,
    val totalUsers: Int = 100_000,
    val batchSize: Int = 1000
) {
    // 1. 单协程串行插入
    suspend fun testSerialInsert(): Long {
        println("开始单协程串行插入...")
        val progressRecorder = ProgressRecorder("serial_progress.txt")
        val startId = progressRecorder.getCurrentProgress()
        val remaining = totalUsers - startId

        if (remaining <= 0) {
            println("所有数据已插入完成")
            return 0
        }

        println("从ID $startId 开始，还需插入 $remaining 条数据")

        val time = measureTimeMillis {
            for (id in startId until totalUsers) {
                val user = UserDataGenerator.generateUsers(id, 1).first()
                dbHelper.insertUser(user)

                if (id % 1000 == 0) {
                    progressRecorder.updateProgress(id + 1)
                    println("已插入 ${id + 1} 条数据")
                }
            }
        }

        progressRecorder.updateProgress(totalUsers)
        return time
    }

    // 2. 无限制并发插入
    suspend fun testUnlimitedConcurrentInsert(): Long {
        println("开始无限制并发插入...")
        val progressRecorder = ProgressRecorder("unlimited_progress.txt")
        val startId = progressRecorder.getCurrentProgress()
        val remaining = totalUsers - startId

        if (remaining <= 0) {
            println("所有数据已插入完成")
            return 0
        }

        println("从ID $startId 开始，还需插入 $remaining 条数据")

        val time = measureTimeMillis {
            val scope = CoroutineScope(Dispatchers.IO + SupervisorJob())
            val progress = AtomicInteger(startId)

            val batches = (0 until (remaining + batchSize - 1) / batchSize).map { batchNum ->
                val batchStart = startId + batchNum * batchSize
                val batchEnd = minOf(startId + (batchNum + 1) * batchSize, totalUsers)

                scope.async {
                    val users = UserDataGenerator.generateUsers(batchStart, batchEnd - batchStart)
                    val inserted = dbHelper.batchInsertUsers(users)
                    val newProgress = progress.addAndGet(inserted)
                    if (newProgress % 5000 == 0) {
                        println("已插入 $newProgress 条数据")
                    }
                    progressRecorder.updateProgress(newProgress)
                }
            }

            batches.awaitAll()
            scope.cancel()
        }

        progressRecorder.updateProgress(totalUsers)
        return time
    }

    // 3. 有限制并发 + 批处理插入（核心功能）
    @OptIn(ExperimentalCoroutinesApi::class)
    suspend fun testLimitedConcurrentInsert(maxConcurrency: Int = 15): Long {
        println("开始有限制并发($maxConcurrency) + 批处理插入...")
        val progressRecorder = ProgressRecorder("limited_progress.txt")
        val startId = progressRecorder.getCurrentProgress()
        val remaining = totalUsers - startId

        if (remaining <= 0) {
            println("所有数据已插入完成")
            return 0
        }

        println("从ID $startId 开始，还需插入 $remaining 条数据")

        val semaphore = Semaphore(maxConcurrency)
        val scope = CoroutineScope(Dispatchers.IO + SupervisorJob())
        val progress = AtomicInteger(startId)
        val time = measureTimeMillis {
            val batches = (0 until (remaining + batchSize - 1) / batchSize).map { batchNum ->
                val batchStart = startId + batchNum * batchSize
                val batchEnd = minOf(startId + (batchNum + 1) * batchSize, totalUsers)

                scope.async {
                    semaphore.withPermit {
                        if (!isActive) return@withPermit

                        val users = UserDataGenerator.generateUsers(batchStart, batchEnd - batchStart)
                        val inserted = dbHelper.batchInsertUsers(users)
                        val newProgress = progress.addAndGet(inserted)

                        if (newProgress % 5000 == 0) {
                            println("已插入 $newProgress 条数据")
                        }
                        progressRecorder.updateProgress(newProgress)
                    }
                }
            }

            batches.awaitAll()
            scope.cancel()
        }

        progressRecorder.updateProgress(totalUsers)
        return time
    }

    fun resetAllProgress() {
        listOf("serial_progress.txt", "unlimited_progress.txt", "limited_progress.txt").forEach {
            ProgressRecorder(it).reset()
        }
    }
}

// 配置Hikari连接池
fun createHikariDataSource(
    jdbcUrl: String,
    username: String,
    password: String,
    maxPoolSize: Int = 20
): HikariDataSource {
    val config = HikariConfig().apply {
        this.jdbcUrl = jdbcUrl
        this.username = username
        this.password = password
        this.maximumPoolSize = maxPoolSize // 最大连接数
        this.minimumIdle = 5 // 最小空闲连接
        this.idleTimeout = 300000 // 空闲超时（5分钟）
        this.connectionTimeout = 30000 // 连接超时（30秒）
        this.maxLifetime = 1800000 // 连接最大生命周期（30分钟）
        this.leakDetectionThreshold = 60000 // 泄漏检测（60秒）
    }
    return HikariDataSource(config)
}

// 主函数
//===== 性能对比 =====
//总数据量: 100000条，批处理大小: 1000
//单协程串行: 234648ms (234.648秒)
//无限制并发: 2859ms (2.859秒)
//有限制并发(15): 2960ms (2.96秒)
//====================
//有限制并发 vs 单协程: 提升 98.74%
//有限制并发 vs 无限制: 下降 3.53%
fun main() = runBlocking {
    // 数据库配置（替换为实际环境）
    val jdbcUrl = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true"
    val username = "root"
    val password = "123456"
    val maxConnections = 20 // 连接池最大连接数

    // 初始化Hikari连接池
    val dataSource = createHikariDataSource(jdbcUrl, username, password, maxConnections)
    val dbHelper = UserDatabaseHelper(dataSource)

    try {
        // 初始化表结构
        dbHelper.createTableIfNotExists()
        dbHelper.truncateTable()

        val testManager = InsertTestManager(dbHelper)
        testManager.resetAllProgress()

        // 依次测试三种插入方式
        val serialTime = testManager.testSerialInsert()
        println("单协程串行插入耗时: ${serialTime}ms")

        dbHelper.truncateTable()
        testManager.resetAllProgress()
        val unlimitedTime = testManager.testUnlimitedConcurrentInsert()
        println("无限制并发插入耗时: ${unlimitedTime}ms")

        dbHelper.truncateTable()
        testManager.resetAllProgress()
        val limitedTime = testManager.testLimitedConcurrentInsert(15) // 限制15个并发
        println("有限制并发插入耗时: ${limitedTime}ms")

        // 输出对比结果
        println("\n===== 性能对比 =====")
        println("总数据量: ${testManager.totalUsers}条，批处理大小: ${testManager.batchSize}")
        println("单协程串行: ${serialTime}ms (${serialTime / 1000.0}秒)")
        println("无限制并发: ${unlimitedTime}ms (${unlimitedTime / 1000.0}秒)")
        println("有限制并发(15): ${limitedTime}ms (${limitedTime / 1000.0}秒)")
        println("====================")
        println("有限制并发 vs 单协程: 提升 ${String.format("%.2f", (1 - limitedTime.toDouble() / serialTime) * 100)}%")
        println(
            "有限制并发 vs 无限制: ${if (limitedTime < unlimitedTime) "提升" else "下降"} ${
                String.format(
                    "%.2f",
                    kotlin.math.abs(1 - limitedTime.toDouble() / unlimitedTime) * 100
                )
            }%"
        )

    } catch (e: Exception) {
        println("执行失败: ${e.message}")
        e.printStackTrace()
    } finally {
        dataSource.close() // 关闭连接池
    }
}


